﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Register.IDAL;
using Register.DBUtility;
using System.Data.SqlClient;
using System.Data;
using Register.Model;

namespace Register.DAL
{
    /// <summary>
    /// FileName: AnnouncementDAL.cs
    /// CLRVersion: 4.0.30319.269
    /// Author: WangKunLun
    /// Corporation: 
    /// Description: 
    /// DateTime: 2012/6/30 17:39:34
    /// </summary>
    public class AnnouncementDAL : IAnnouncementDAL
    {
        private string tableName = "System_announcement";

        public int AddAnnouncement(Model.Announcement model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into System_announcement(");
            strSql.Append("Announcement_ID,Announcement_Title,Announcement_Content,Announcement_NumberViews,Announcement_AddTime,Announcement_Recoder,Announcement_Status)");
            strSql.Append(" values (");
            strSql.Append("@Announcement_ID,@Announcement_Title,@Announcement_Content,@Announcement_NumberViews,@Announcement_AddTime,@Announcement_Recoder,@Announcement_Status)");
            SqlParameter[] parameters = {
					new SqlParameter("@Announcement_ID", SqlDbType.Char,14),
					new SqlParameter("@Announcement_Title", SqlDbType.VarChar,100),
					new SqlParameter("@Announcement_Content", SqlDbType.Text),
					new SqlParameter("@Announcement_NumberViews", SqlDbType.Int),
					new SqlParameter("@Announcement_AddTime", SqlDbType.DateTime),
					new SqlParameter("@Announcement_Recoder", SqlDbType.VarChar,20),
					new SqlParameter("@Announcement_Status", SqlDbType.Char,1)};
            parameters[0].Value = model.Announcement_ID;
            parameters[1].Value = model.Announcement_Title;
            parameters[2].Value = model.Announcement_Content;
            parameters[3].Value = model.Announcement_NumberViews;
            parameters[4].Value = model.Announcement_AddTime;
            parameters[5].Value = model.Announcement_Recoder;
            parameters[6].Value = model.Announcement_Status;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

        }

        public int DeleteAnnouncement(string announcementID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_announcement set ");
            strSql.Append("Announcement_Status=0 ");
            strSql.Append(" where Announcement_ID=@Announcement_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Announcement_ID", SqlDbType.Char,14)};
            parameters[0].Value = announcementID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public int UpdateAnnouncement(Model.Announcement model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_announcement set ");
            strSql.Append("Announcement_Title=@Announcement_Title,");
            strSql.Append("Announcement_Content=@Announcement_Content ");
            strSql.Append(" where Announcement_ID=@Announcement_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Announcement_Title", SqlDbType.VarChar,100),
					new SqlParameter("@Announcement_Content", SqlDbType.Text),
					new SqlParameter("@Announcement_ID", SqlDbType.Char,14)};
            parameters[0].Value = model.Announcement_Title;
            parameters[1].Value = model.Announcement_Content;
            parameters[2].Value = model.Announcement_ID;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public List<Model.Announcement> GetPagedAnnouncement(int size, int pageIndex, string where)
        {
            return DataSetToList(DbHelperSQL.getPagedData(" * ", " Announcement_AddTime ", where, tableName, OrderBy.DESC, pageIndex, size));
        }


        public bool Exists(string Announcement_Title)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from System_announcement");
            strSql.Append(" where Announcement_Title=@Announcement_Title ");
            SqlParameter[] parameters = {
					new SqlParameter("@Announcement_Title", SqlDbType.VarChar)};
            parameters[0].Value = Announcement_Title;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        public Model.Announcement GetAnnouncement(string AnnouncementID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Announcement_ID,Announcement_Title,Announcement_Content,Announcement_NumberViews,Announcement_AddTime,Announcement_Recoder,Announcement_Status from System_announcement ");
            strSql.Append(" where Announcement_ID=@Announcement_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Announcement_ID", SqlDbType.Char,14)};
            parameters[0].Value = AnnouncementID;
            return DataSetToList(DbHelperSQL.Query(strSql.ToString(), parameters)).First();
        }


        private List<Model.Announcement> DataSetToList(DataSet ds)
        {
            List<Model.Announcement> list = new List<Model.Announcement>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Announcement announcement = new Announcement();
                announcement.Announcement_ID = (String)dr["Announcement_ID"];
                announcement.Announcement_Title = (String)dr["Announcement_Title"];
                announcement.Announcement_Content = (String)dr["Announcement_Content"];
                announcement.Announcement_NumberViews = (int)dr["Announcement_NumberViews"];
                announcement.Announcement_AddTime = (DateTime)dr["Announcement_AddTime"];
                announcement.Announcement_Recoder = (String)dr["Announcement_Recoder"];
                announcement.Announcement_Status = (String)dr["Announcement_Status"];
                list.Add(announcement);
            }
            return list;
        }

        public int GetAnnouncementCount(string where)
        {
            int count = 0;
            StringBuilder str = new StringBuilder();
            str.Append(" select count(*) from ");
            str.Append(tableName);
            if (where != null)
            {
                str.Append(" where ");
                str.Append(where);
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            else
            {
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            return count;
        }
    }
}
